import json
import mysql.connector
from datetime import date, datetime,timedelta
import hashlib
from config import MYSQL_CONFIG

class DateEncoder(json.JSONEncoder):
    """处理日期对象的JSON序列化问题"""
    def default(self, obj):
        if isinstance(obj, (date, datetime)):
            return obj.strftime('%Y-%m-%d')
        return super().default(obj)

def fetch_mysql_data():
    """从MySQL获取数据并处理日期序列化问题"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor(dictionary=True)
        cursor.execute("SELECT rybh as id, jsh as room_number, xm as name, jjsrq as join_date, FXDJ, BHLX FROM persons")
        persons_data = cursor.fetchall()

        # 原子文件写入
        tmp_path = 'person_data.tmp'
        with open(tmp_path, 'w', encoding='utf-8') as f:
            json.dump(persons_data, f, ensure_ascii=False, indent=2, cls=DateEncoder)
        return persons_data
    except mysql.connector.Error as db_err:
        print(f"数据库错误: {str(db_err)}")
        return None
    except Exception as e:
        print(f"数据获取异常: {str(e)}")
        return None
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

def get_schedule_by_date(room_number, week_date):
    """获取指定周的值班表，分别获取每种数据类型的最新记录"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor(dictionary=True)

        # 计算指定周的周一日期
        target_date = datetime.strptime(week_date, '%Y-%m-%d').date()
        days_to_monday = target_date.weekday()
        monday = target_date - timedelta(days=days_to_monday)

        # 获取最新的schedule_data记录
        schedule_query = """
            SELECT schedule_data, record_date 
            FROM schedules 
            WHERE room_number = %s 
            AND week_start_date = %s
            AND schedule_data IS NOT NULL
            ORDER BY record_date DESC
            LIMIT 1
        """
        cursor.execute(schedule_query, (room_number, monday))
        schedule_result = cursor.fetchone()
        
        # 获取最新的bed_data记录
        bed_query = """
            SELECT bed_data, record_date 
            FROM schedules 
            WHERE room_number = %s 
            AND week_start_date = %s
            AND bed_data IS NOT NULL
            ORDER BY record_date DESC
            LIMIT 1
        """
        cursor.execute(bed_query, (room_number, monday))
        bed_result = cursor.fetchone()
        
        # 获取最新的seat_data记录
        seat_query = """
            SELECT seat_data, record_date 
            FROM schedules 
            WHERE room_number = %s 
            AND week_start_date = %s
            AND seat_data IS NOT NULL
            ORDER BY record_date DESC
            LIMIT 1
        """
        cursor.execute(seat_query, (room_number, monday))
        seat_result = cursor.fetchone()

        # 确保读取所有结果
        while cursor.nextset():
            pass

        # 如果没有任何数据，返回None
        if not schedule_result and not bed_result and not seat_result:
            return None
            
        # 合并结果
        result = {}
        if schedule_result:
            result['schedule_data'] = schedule_result['schedule_data']
        if bed_result:
            result['bed_data'] = bed_result['bed_data']
        if seat_result:
            result['seat_data'] = seat_result['seat_data']
            
        return result
    except mysql.connector.Error as db_err:
        print(f"数据库查询错误: {str(db_err)}")
        return None
    except Exception as e:
        print(f"获取值班表异常: {str(e)}")
        return None
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

def save_schedule_to_db(room_number, schedule_data, target_date=None):
    """将值班表保存到数据库"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()

        # 使用传入日期或当前日期
        today = datetime.strptime(target_date, '%Y-%m-%d').date() if target_date else datetime.now().date()
        days_to_monday = today.weekday()
        monday = today - timedelta(days=days_to_monday)
        
        # 记录日期使用当前日期或传入的目标日期
        if target_date:
            record_date = target_date
        else:
            record_date = today

        # 将排班数据转换为JSON字符串
        schedule_json = json.dumps(schedule_data, ensure_ascii=False, cls=DateEncoder)

        # 使用ON DUPLICATE KEY UPDATE处理重复数据
        query = """
            INSERT INTO schedules (room_number, week_start_date, schedule_data, record_date)
            VALUES (%s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE schedule_data = VALUES(schedule_data)
        """
        cursor.execute(query, (room_number, monday, schedule_json, record_date))
        conn.commit()

        return True
    except mysql.connector.Error as db_err:
        print(f"数据库保存错误: {str(db_err)}")
        return False
    except Exception as e:
        print(f"保存值班表异常: {str(e)}")
        return False
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

def save_bed_layout_to_db(room_number, bed_data, target_date=None):
    """将铺位布局保存到数据库，只更新顺序字段，保留原有字段"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()

        # 使用传入日期或当前日期
        today = datetime.strptime(target_date, '%Y-%m-%d').date() if target_date else datetime.now().date()
        days_to_monday = today.weekday()
        monday = today - timedelta(days=days_to_monday)
        
        # 记录日期使用当前日期或传入的目标日期
        if target_date:
            record_date = target_date
        else:
            record_date = today
            
        # 先查询现有数据
        select_query = """
            SELECT bed_data FROM schedules 
            WHERE room_number = %s AND week_start_date = %s
            ORDER BY record_date DESC LIMIT 1
        """
        cursor.execute(select_query, (room_number, monday))
        existing_result = cursor.fetchone()
        
        # 如果存在现有数据，合并新数据和旧数据
        if existing_result and existing_result[0]:
            try:
                existing_bed_data = json.loads(existing_result[0])
                # 创建一个映射，以便快速查找现有数据中的字段
                existing_person_map = {}
                for bed in existing_bed_data:
                    if bed and 'id' in bed:
                        existing_person_map[bed['id']] = bed
                
                # 合并数据，只更新顺序字段，保留其他字段
                merged_bed_data = []
                for bed in bed_data:
                    if bed and 'id' in bed:
                        existing_bed = existing_person_map.get(bed['id'], {})
                        # 保留现有数据中的所有字段，只更新位置
                        merged_bed = {
                            **existing_bed,
                            'position': bed['position'],
                            'FXDJ': bed.get('FXDJ', existing_bed.get('FXDJ', 0)),
                            'BHLX': bed.get('BHLX', existing_bed.get('BHLX', 0))
                        }
                        merged_bed_data.append(merged_bed)
                    else:
                        merged_bed_data.append(bed)
                
                bed_data = merged_bed_data
            except (json.JSONDecodeError, TypeError) as e:
                print(f"解析现有铺位数据错误: {str(e)}")
        
        # 将数据转换为JSON字符串
        bed_json = json.dumps(bed_data, ensure_ascii=False, cls=DateEncoder)

        query = """
            INSERT INTO schedules (room_number, week_start_date, bed_data, record_date)
            VALUES (%s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE bed_data = VALUES(bed_data)
        """
        cursor.execute(query, (room_number, monday, bed_json, record_date))
        conn.commit()

        return True
    except mysql.connector.Error as db_err:
        print(f"数据库保存错误: {str(db_err)}")
        return False
    except Exception as e:
        print(f"保存铺位布局异常: {str(e)}")
        return False
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

def save_seat_layout_to_db(room_number, seat_data, target_date=None):
    """将座位布局保存到数据库，只更新顺序字段，保留原有字段"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()

        # 使用传入日期或当前日期
        today = datetime.strptime(target_date, '%Y-%m-%d').date() if target_date else datetime.now().date()
        days_to_monday = today.weekday()
        monday = today - timedelta(days=days_to_monday)
        
        # 记录日期使用当前日期或传入的目标日期
        if target_date:
            record_date = target_date
        else:
            record_date = today
            
        # 先查询现有数据
        select_query = """
            SELECT seat_data FROM schedules 
            WHERE room_number = %s AND week_start_date = %s
            ORDER BY record_date DESC LIMIT 1
        """
        cursor.execute(select_query, (room_number, monday))
        existing_result = cursor.fetchone()
        
        # 如果存在现有数据，合并新数据和旧数据
        if existing_result and existing_result[0]:
            try:
                existing_seat_data = json.loads(existing_result[0])
                # 创建一个映射，以便快速查找现有数据中的字段
                existing_person_map = {}
                for seat in existing_seat_data:
                    if seat and 'id' in seat:
                        existing_person_map[seat['id']] = seat
                
                # 合并数据，只更新顺序字段，保留其他字段
                merged_seat_data = []
                for seat in seat_data:
                    if seat and 'id' in seat:
                        existing_seat = existing_person_map.get(seat['id'], {})
                        # 保留现有数据中的所有字段，只更新位置
                        merged_seat = {
                            **existing_seat,
                            'position': seat['position'],
                            'FXDJ': seat.get('FXDJ', existing_seat.get('FXDJ', 0)),
                            'BHLX': seat.get('BHLX', existing_seat.get('BHLX', 0))
                        }
                        merged_seat_data.append(merged_seat)
                    else:
                        merged_seat_data.append(seat)
                
                seat_data = merged_seat_data
            except (json.JSONDecodeError, TypeError) as e:
                print(f"解析现有座位数据错误: {str(e)}")
        
        # 将数据转换为JSON字符串
        seat_json = json.dumps(seat_data, ensure_ascii=False, cls=DateEncoder)

        query = """
            INSERT INTO schedules (room_number, week_start_date, seat_data, record_date)
            VALUES (%s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE seat_data = VALUES(seat_data)
        """
        cursor.execute(query, (room_number, monday, seat_json, record_date))
        conn.commit()

        return True
    except mysql.connector.Error as db_err:
        print(f"数据库保存错误: {str(db_err)}")
        return False
    except Exception as e:
        print(f"保存座位布局异常: {str(e)}")
        return False
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

# 用户相关的数据库操作函数
def hash_password(password):
    """对密码进行哈希处理"""
    return hashlib.sha256(password.encode()).hexdigest()

def create_user(username, password, room_numbers, user_type='normal', full_name=None, phone=None, role_id=None):
    """创建新用户（注册）"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        # 对密码进行哈希处理
        hashed_password = hash_password(password)
        
        # 将房间号列表转换为逗号分隔的字符串
        if isinstance(room_numbers, list):
            room_numbers = ','.join(map(str, room_numbers))
        
        query = """
            INSERT INTO login (username, password, room_numbers, user_type, full_name, phone, role_id)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
        """
        cursor.execute(query, (username, hashed_password, room_numbers, user_type, full_name, phone, role_id))
        conn.commit()
        
        return True
    except mysql.connector.Error as db_err:
        print(f"用户创建错误: {str(db_err)}")
        return False
    except Exception as e:
        print(f"用户创建异常: {str(e)}")
        return False
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

def verify_user(username, password):
    """验证用户登录"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        # 对输入的密码进行哈希处理
        hashed_password = hash_password(password)
        
        query = """
            SELECT id, username, room_numbers, user_type, role_id
            FROM login
            WHERE username = %s AND password = %s
        """
        cursor.execute(query, (username, hashed_password))
        user = cursor.fetchone()
        
        if user:
            # 更新最后登录时间
            update_last_login(user['id'])
            return user
        return None
    except mysql.connector.Error as db_err:
        print(f"用户验证错误: {str(db_err)}")
        return None
    except Exception as e:
        print(f"用户验证异常: {str(e)}")
        return None
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

def get_user_by_id(user_id):
    """根据ID获取用户信息"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        query = """
            SELECT id, username, room_numbers, user_type, created_at, last_login
            FROM login
            WHERE id = %s
        """
        cursor.execute(query, (user_id,))
        return cursor.fetchone()
    except mysql.connector.Error as db_err:
        print(f"获取用户信息错误: {str(db_err)}")
        return None
    except Exception as e:
        print(f"获取用户信息异常: {str(e)}")
        return None
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

def get_user_by_username(username):
    """根据用户名获取用户信息"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        query = """
            SELECT id, username, room_numbers, user_type, created_at, last_login
            FROM login
            WHERE username = %s
        """
        cursor.execute(query, (username,))
        return cursor.fetchone()
    except mysql.connector.Error as db_err:
        print(f"获取用户信息错误: {str(db_err)}")
        return None
    except Exception as e:
        print(f"获取用户信息异常: {str(e)}")
        return None
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

def update_last_login(user_id):
    """更新用户最后登录时间"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        query = """
            UPDATE login
            SET last_login = CURRENT_TIMESTAMP
            WHERE id = %s
        """
        cursor.execute(query, (user_id,))
        conn.commit()
        
        return True
    except mysql.connector.Error as db_err:
        print(f"更新登录时间错误: {str(db_err)}")
        return False
    except Exception as e:
        print(f"更新登录时间异常: {str(e)}")
        return False
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

def check_username_exists(username):
    """检查用户名是否已存在"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        query = """
            SELECT COUNT(*) FROM login WHERE username = %s
        """
        cursor.execute(query, (username,))
        count = cursor.fetchone()[0]
        
        return count > 0
    except mysql.connector.Error as db_err:
        print(f"检查用户名错误: {str(db_err)}")
        return False
    except Exception as e:
        print(f"检查用户名异常: {str(e)}")
        return False
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

def save_seat_change_record(room_number, user_id, operation_time, 
                          change_description, change_reason, seats_data):
    """
    保存座位变更记录到seatChange_record表
    参数:
        room_number: 房间号
        user_id: 操作用户ID
        operation_time: 操作时间(ISO格式字符串)
        change_description: 变更说明
        change_reason: 变更理由
        seats_data: 座位变更数据列表
    返回:
        bool: 操作是否成功
    """
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        # 转换时间为数据库兼容格式
        if isinstance(operation_time, str):
            try:
                op_time = datetime.strptime(operation_time, '%Y-%m-%dT%H:%M:%S.%f')
            except ValueError:
                op_time = datetime.strptime(operation_time, '%Y-%m-%dT%H:%M:%S')
        else:
            op_time = operation_time
        
        # 序列化seats数据
        seats_json = json.dumps(seats_data, ensure_ascii=False, cls=DateEncoder)
        
        query = """
            INSERT INTO seatChange_record (
                room_number, user_id, operation_time,
                change_description, change_reason, seats_data
            ) VALUES (%s, %s, %s, %s, %s, %s)
        """
        cursor.execute(query, (
            room_number,
            user_id,
            op_time,
            change_description,
            change_reason,
            seats_json
        ))
        conn.commit()
        return True
        
    except mysql.connector.Error as db_err:
        print(f"保存座位变更记录数据库错误: {str(db_err)}")
        if conn:
            conn.rollback()
        return False
    except Exception as e:
        print(f"保存座位变更记录异常: {str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        if cursor: 
            cursor.close()
        if conn and conn.is_connected(): 
            conn.close()

def save_bed_change_record(room_number, user_id, operation_time, 
                          change_description, change_reason, beds_data):
    """
    保存铺位变更记录到bedChange_record表
    参数:
        room_number: 房间号
        user_id: 操作用户ID
        operation_time: 操作时间(ISO格式字符串)
        change_description: 变更说明
        change_reason: 变更理由
        beds_data: 铺位变更数据列表
    返回:
        bool: 操作是否成功
    """
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        # 转换时间为数据库兼容格式，处理可能包含毫秒的时间字符串
        if isinstance(operation_time, str):
            try:
                # 尝试包含毫秒的格式
                op_time = datetime.strptime(operation_time, '%Y-%m-%dT%H:%M:%S.%f')
            except ValueError:
                # 尝试不含毫秒的格式
                op_time = datetime.strptime(operation_time, '%Y-%m-%dT%H:%M:%S')
        else:
            op_time = operation_time
        
        # 序列化beds数据
        beds_json = json.dumps(beds_data, ensure_ascii=False, cls=DateEncoder)
        
        query = """
            INSERT INTO bedChange_record (
                room_number, user_id, operation_time,
                change_description, change_reason, beds_data
            ) VALUES (%s, %s, %s, %s, %s, %s)
        """
        cursor.execute(query, (
            room_number,
            user_id,
            op_time,
            change_description,
            change_reason,
            beds_json
        ))
        conn.commit()
        return True
        
    except mysql.connector.Error as db_err:
        print(f"保存铺位变更记录数据库错误: {str(db_err)}")
        if conn:
            conn.rollback()
        return False
    except Exception as e:
        print(f"保存铺位变更记录异常: {str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        if cursor: 
            cursor.close()
        if conn and conn.is_connected(): 
            conn.close()

def get_all_room_numbers():
    """获取所有可用的房间号"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        # 从persons表中获取所有唯一的房间号
        query = """
            SELECT DISTINCT jsh FROM persons ORDER BY jsh
        """
        cursor.execute(query)
        rooms = [row[0] for row in cursor.fetchall()]
        
        return rooms
    except mysql.connector.Error as db_err:
        print(f"获取房间号错误: {str(db_err)}")
        return []
    except Exception as e:
        print(f"获取房间号异常: {str(e)}")
        return []
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

def get_record_count(query):
    """获取符合条件的记录总数"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        # 构建基础查询条件
        conditions = []
        params = []
        
        # 处理记录类型条件
        if 'type' in query:
            record_type = query['type']
            if record_type == 'seat':
                table = 'seatChange_record'
            elif record_type == 'bed':
                table = 'bedChange_record'
            elif record_type == 'schedule':
                table = 'scheduleChange_record'
            else:
                # 默认查询所有表
                table = None
        else:
            # 默认查询所有表
            table = None
        
        # 处理时间范围条件
        time_conditions = []
        if 'create_time__gte' in query:
            time_conditions.append("operation_time >= %s")
            params.append(query['create_time__gte'])
        if 'create_time__lte' in query:
            time_conditions.append("operation_time <= %s")
            params.append(query['create_time__lte'])
        
        # 处理房间号条件
        if 'room_number__in' in query:
            room_numbers = query['room_number__in']
            if isinstance(room_numbers, str):
                room_numbers = [room_numbers]
            room_conditions = "room_number IN (" + ",".join(["%s"] * len(room_numbers)) + ")"
            params.extend(room_numbers)
        else:
            room_conditions = None
        
        # 构建最终查询
        if table:
            # 单表查询
            sql = f"SELECT COUNT(*) FROM {table}"
            where_clauses = []
            if time_conditions:
                where_clauses.append(" AND ".join(time_conditions))
            if room_conditions:
                where_clauses.append(room_conditions)
            if where_clauses:
                sql += " WHERE " + " AND ".join(where_clauses)
        else:
            # 多表联合查询
            sql = """
                SELECT SUM(cnt) FROM (
                    SELECT COUNT(*) as cnt FROM seatChange_record
                    WHERE {}
                    UNION ALL
                    SELECT COUNT(*) as cnt FROM bedChange_record
                    WHERE {}
                    UNION ALL
                    SELECT COUNT(*) as cnt FROM scheduleChange_record
                    WHERE {}
                ) as combined_counts
            """.format(
                " AND ".join(time_conditions) if time_conditions else "1=1",
                " AND ".join(time_conditions) if time_conditions else "1=1",
                " AND ".join(time_conditions) if time_conditions else "1=1"
            )
            if room_conditions:
                sql = sql.replace("WHERE 1=1", f"WHERE {room_conditions}")
        
        cursor.execute(sql, params)
        return cursor.fetchone()[0]
        
    except mysql.connector.Error as db_err:
        print(f"获取记录总数错误: {str(db_err)}")
        return 0
    except Exception as e:
        print(f"获取记录总数异常: {str(e)}")
        return 0
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

def get_records(query, sort='time', skip=0, limit=20):
    """获取调整记录"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        # 处理排序字段映射
        sort_mapping = {
            'type': 'type',
            'room': 'room_number',
            'content': 'change_description',
            'operator': 'user_id',
            'time': 'operation_time'
        }
        sort_field = sort_mapping.get(sort, 'operation_time')
        
        # 处理记录类型条件
        record_type = query.get('type', '')
        if record_type == 'seat':
            tables = ['seatChange_record']
        elif record_type == 'bed':
            tables = ['bedChange_record']
        elif record_type == 'schedule':
            tables = ['scheduleChange_record']
        else:
            tables = ['seatChange_record', 'bedChange_record', 'scheduleChange_record']
        
        # 构建基础查询条件
        conditions = []
        params = []
        
        # 处理时间范围条件
        if 'create_time__gte' in query:
            conditions.append("operation_time >= %s")
            params.append(query['create_time__gte'])
        if 'create_time__lte' in query:
            conditions.append("operation_time <= %s")
            params.append(query['create_time__lte'])
        
        # 处理房间号条件
        if 'room_number__in' in query:
            room_numbers = query['room_number__in']
            if isinstance(room_numbers, str):
                room_numbers = [room_numbers]
            conditions.append("room_number IN (" + ",".join(["%s"] * len(room_numbers)) + ")")
            params.extend(room_numbers)
        
        # 构建UNION ALL查询
        queries = []
        for table in tables:
            table_query = f"""
                SELECT 
                    '{table.split('_')[0]}' as type,
                    room_number,
                    user_id,
                    operation_time,
                    change_description,
                    change_reason,
                    NULL as seats_data,
                    NULL as beds_data,
                    NULL as schedule_data
                FROM {table}
            """
            if conditions:
                table_query += " WHERE " + " AND ".join(conditions)
            queries.append(table_query)
        
        # 完整查询
        sql = "(" + ") UNION ALL (".join(queries) + ")"
        sql = f"""
            SELECT * FROM (
                {sql}
            ) as combined_records
            ORDER BY {sort_field} DESC
            LIMIT %s OFFSET %s
        """
        params.extend([limit, skip])
        
        cursor.execute(sql, params)
        records = cursor.fetchall()
        
        # 处理数据字段
        for record in records:
            if record['type'] == 'seatChange':
                record['type'] = 'seat'
                record['data'] = json.loads(record['seats_data']) if record['seats_data'] else []
            elif record['type'] == 'bedChange':
                record['type'] = 'bed'
                record['data'] = json.loads(record['beds_data']) if record['beds_data'] else []
            elif record['type'] == 'scheduleChange':
                record['type'] = 'schedule'
                record['data'] = json.loads(record['schedule_data']) if record['schedule_data'] else []
            
            # 添加用户信息
            user = get_user_by_id(record['user_id'])
            if user:
                record['username'] = user['username']
        
        return records
        
    except mysql.connector.Error as db_err:
        print(f"获取记录错误: {str(db_err)}")
        return []
    except Exception as e:
        print(f"获取记录异常: {str(e)}")
        return []
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

def save_schedule_change_record(room_number, user_id, change_description, change_reason, schedule_data):
    """
    保存值班表变更记录到scheduleChange_record表
    参数:
        room_number: 房间号
        user_id: 操作用户ID
        change_description: 变更说明
        change_reason: 变更理由
        schedule_data: 排班变更数据
    返回:
        bool: 操作是否成功
    """
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        # 序列化schedule数据
        schedule_json = json.dumps(schedule_data, ensure_ascii=False, cls=DateEncoder)
        
        query = """
            INSERT INTO scheduleChange_record (
                room_number, user_id, operation_time,
                change_description, change_reason, schedule_data
            ) VALUES (%s, %s, NOW(), %s, %s, %s)
        """
        cursor.execute(query, (
            room_number,
            user_id,
            change_description,
            change_reason,
            schedule_json
        ))
        conn.commit()
        return True
        
    except mysql.connector.Error as db_err:
        print(f"保存值班表变更记录数据库错误: {str(db_err)}")
        if conn:
            conn.rollback()
        return False
    except Exception as e:
        print(f"保存值班表变更记录异常: {str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        if cursor: 
            cursor.close()
        if conn and conn.is_connected(): 
            conn.close()

def update_user_information(user_id, full_name=None, phone=None, role_id=None):
    """
    更新用户信息
    参数:
        user_id: 用户ID
        full_name: 姓名
        phone: 电话
        role_id: 岗位ID
    返回:
        bool: 操作是否成功
    """
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        # 构建更新语句
        updates = []
        params = []
        
        if full_name is not None:
            updates.append("full_name = %s")
            params.append(full_name)
        
        if phone is not None:
            updates.append("phone = %s")
            params.append(phone)
        
        if role_id is not None:
            updates.append("role_id = %s")
            params.append(role_id)
        
        # 如果没有要更新的字段，直接返回True
        if not updates:
            return True
        
        # 添加用户ID到参数列表
        params.append(user_id)
        
        query = "UPDATE login SET " + ", ".join(updates) + " WHERE id = %s"
        cursor.execute(query, params)
        conn.commit()
        return True
        
    except mysql.connector.Error as db_err:
        print(f"更新用户信息数据库错误: {str(db_err)}")
        if conn:
            conn.rollback()
        return False
    except Exception as e:
        print(f"更新用户信息异常: {str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        if cursor: 
            cursor.close()
        if conn and conn.is_connected(): 
            conn.close()

def update_user_room_permissions(user_id, room_numbers):
    """
    更新用户的房间权限
    参数:
        user_id: 用户ID
        room_numbers: 房间号列表
    返回:
        bool: 操作是否成功
    """
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        # 将房间号列表转换为逗号分隔的字符串
        room_numbers_str = ','.join(room_numbers) if room_numbers else ''
        
        query = "UPDATE login SET room_numbers = %s WHERE id = %s"
        cursor.execute(query, (room_numbers_str, user_id))
        conn.commit()
        return True
        
    except mysql.connector.Error as db_err:
        print(f"更新用户房间权限数据库错误: {str(db_err)}")
        if conn:
            conn.rollback()
        return False
    except Exception as e:
        print(f"更新用户房间权限异常: {str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        if cursor: 
            cursor.close()
        if conn and conn.is_connected(): 
            conn.close()

def get_all_users():
    """
    获取所有用户信息
    返回:
        list: 用户信息列表
    """
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        query = "SELECT id, username, user_type, room_numbers, full_name, phone, role_id FROM login"
        cursor.execute(query)
        users = cursor.fetchall()
        
        # 处理房间号字段
        for user in users:
            if user['room_numbers']:
                user['room_numbers'] = user['room_numbers'].split(',')
            else:
                user['room_numbers'] = []
        
        return users
        
    except mysql.connector.Error as db_err:
        print(f"获取所有用户信息数据库错误: {str(db_err)}")
        return []
    except Exception as e:
        print(f"获取所有用户信息异常: {str(e)}")
        return []
    finally:
        if cursor: 
            cursor.close()
        if conn and conn.is_connected(): 
            conn.close()

def delete_user(user_id):
    """
    删除用户
    参数:
        user_id: 用户ID
    返回:
        bool: 操作是否成功
    """
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor()
        
        query = "DELETE FROM login WHERE id = %s"
        cursor.execute(query, (user_id,))
        conn.commit()
        return True
        
    except mysql.connector.Error as db_err:
        print(f"删除用户数据库错误: {str(db_err)}")
        if conn:
            conn.rollback()
        return False
    except Exception as e:
        print(f"删除用户异常: {str(e)}")
        if conn:
            conn.rollback()
        return False
    finally:
        if cursor: 
            cursor.close()
        if conn and conn.is_connected(): 
            conn.close()

def get_all_roles():
    """获取所有角色"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        query = "SELECT id, name FROM roles ORDER BY id"
        cursor.execute(query)
        return cursor.fetchall()
    except mysql.connector.Error as db_err:
        print(f"获取角色列表错误: {str(db_err)}")
        return []
    except Exception as e:
        print(f"获取角色列表异常: {str(e)}")
        return []
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()

def get_persons_by_room(room_number):
    """根据房间号获取该房间内的所有人员信息"""
    conn = None
    cursor = None
    try:
        conn = mysql.connector.connect(**MYSQL_CONFIG)
        cursor = conn.cursor(dictionary=True)
        
        # 查询指定房间号的所有人员
        query = """
            SELECT rybh as id, xm as name FROM persons WHERE jsh = %s ORDER BY xm
        """
        cursor.execute(query, (room_number,))
        persons = cursor.fetchall()
        
        return persons
    except mysql.connector.Error as db_err:
        print(f"获取房间人员信息错误: {str(db_err)}")
        return []
    except Exception as e:
        print(f"获取房间人员信息异常: {str(e)}")
        return []
    finally:
        if cursor: cursor.close()
        if conn and conn.is_connected(): conn.close()